﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class Orders : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection();
    SqlCommand cmd;
    SqlDataAdapter da;
    DataTable dt;
    DataSet ds;

    protected void Page_Load(object sender, EventArgs e)
    {
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        if (!IsPostBack)
        {
            try
            {
                LoadOrdersInfor();
                LoadDDLCustomers();
                LoadDDLEmployees();
                LoadDDLShippers();
                ViewState["gv"] = gvOrders.DataSource;
                ViewState["SortDirection"] = "DESC";
            }
            catch (System.Exception ex)
            {
                Response.Redirect("404.aspx");
                //Response.Write("<script>alert('" + "Cannot connect to database!" + "')</script>");
            }
        }

        gvOrders.DataSource = ViewState["gv"];
        gvOrders.DataBind();

        btnUpdate.Attributes.Add("onclick", "if(!confirm('Are you sure?')) return false;");
        btnDelete.Attributes.Add("onclick", "if(!confirm('Are you sure?')) return false;");
    }

    #region MainFunction
    private void LoadOrdersInfor()
    {
        //try
        //{
        con.Open();
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "GetOrders";

        da = new SqlDataAdapter(cmd);
        ds = new DataSet();
        da.Fill(ds);

        gvOrders.AutoGenerateSelectButton = true;
        gvOrders.AllowPaging = true;
        gvOrders.PageSize = 10;

        gvOrders.DataSource = ds.Tables[0];
        gvOrders.DataBind();
        ViewState["gv"] = gvOrders.DataSource;
        con.Close();

        //}
        //catch (System.Exception ex)
        //{
        //    Response.Write("<script>alert('" + "Load Fail!" + "')</script>");
        //}
        //finally
        //{
        //    con.Close();
        //}

    }

    private void ClearData()
    {
        btnUpdate.Enabled = false;
        btnDelete.Enabled = false;
        btnAdd.Enabled = true;
        txtOrderID.Text = "";
        txtOrderDate.Text = "";
        txtRequiredDate.Text = "";
        txtShippedDate.Text = "";
        ddlShipper.SelectedIndex = -1;
        txtFreight.Text = "";
        txtShipName.Text = "";
        txtShipAddress.Text = "";
        txtShipCity.Text = "";
        txtShipRegion.Text = "";
        txtShipPostalCode.Text = "";
        txtSearch.Text = "";
        txtDateSearchFrom.Text = "";
        txtDateSearchTo.Text = "";
        lblNotFound.Text = "";
        LoadOrdersInfor();

    }

    private void AddOrder()
    {
        try
        {
            con.Open();
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "InsertOrder";

            SqlParameter param;

            param = new SqlParameter("@custid", SqlDbType.Int);
            if (ddlCustomer.SelectedIndex == 0)
                param.Value = DBNull.Value;
            else
                param.Value = ddlCustomer.SelectedValue;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@empid", SqlDbType.Int);
            param.Value = ddlEmployee.SelectedValue;
            cmd.Parameters.Add(param);

            DateTime dt;
            dt = DateTime.Parse(txtOrderDate.Text);
            param = new SqlParameter("@orderdate", SqlDbType.DateTime);
            param.Value = dt.ToShortDateString();
            cmd.Parameters.Add(param);

            dt = DateTime.Parse(txtRequiredDate.Text);
            param = new SqlParameter("@requireddate", SqlDbType.DateTime);
            param.Value = dt.ToShortDateString();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shippeddate", SqlDbType.DateTime);
            if (txtShippedDate.Text.Trim().Length == 0)
                param.Value = DBNull.Value;
            else
            {
                dt = DateTime.Parse(txtShippedDate.Text);
                param.Value = dt.ToShortDateString();
            }
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipperid", SqlDbType.Int);
            param.Value = ddlShipper.SelectedValue;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@freight", SqlDbType.Money);
            param.Value = txtFreight.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipname", SqlDbType.NVarChar, 40);
            param.Value = txtShipName.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipaddress", SqlDbType.NVarChar, 60);
            param.Value = txtShipAddress.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipcity", SqlDbType.NVarChar, 15);
            param.Value = txtShipCity.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipregion", SqlDbType.NVarChar, 15);
            if (txtShipRegion.Text.Trim().Length == 0)
                param.Value = DBNull.Value;
            else
                param.Value = txtShipRegion.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shippostalcode", SqlDbType.NVarChar, 10);
            if (txtShipPostalCode.Text.Trim().Length == 0)
                param.Value = DBNull.Value;
            else
                param.Value = int.Parse(txtShipPostalCode.Text);
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipcountry", SqlDbType.NVarChar, 15);
            param.Value = ddlCountry.Text;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
        }
        catch (System.FormatException)
        {
            Response.Write("<script>alert('" + "Invalid Date!" + "')</script>");
        }
        catch (System.Exception ex)
        {
            Response.Write("<script>alert('" + "Cannot add Data, check again!" + "')</script>");
        }
        finally
        {
            con.Close();
        }

    }

    private void UpdateOrder()
    {
        try
        {
            con.Open();
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "UpdateOrder";

            SqlParameter param;

            param = new SqlParameter("@orderid", SqlDbType.Int);
            param.Value = int.Parse(txtOrderID.Text);
            cmd.Parameters.Add(param);

            param = new SqlParameter("@custid", SqlDbType.Int);
            if (ddlCustomer.SelectedIndex == 0)
                param.Value = DBNull.Value;
            else
                param.Value = ddlCustomer.SelectedValue;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@empid", SqlDbType.Int);
            param.Value = ddlEmployee.SelectedValue;
            cmd.Parameters.Add(param);

            DateTime dt;
            dt = DateTime.Parse(txtOrderDate.Text);
            param = new SqlParameter("@orderdate", SqlDbType.DateTime);
            param.Value = dt.ToShortDateString();
            cmd.Parameters.Add(param);

            dt = DateTime.Parse(txtRequiredDate.Text);
            param = new SqlParameter("@requireddate", SqlDbType.DateTime);
            param.Value = dt.ToShortDateString();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shippeddate", SqlDbType.DateTime);
            if (txtShippedDate.Text.Trim().Length == 0)
                param.Value = DBNull.Value;
            else
            {
                dt = DateTime.Parse(txtShippedDate.Text);
                param.Value = dt.ToShortDateString();
            }
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipperid", SqlDbType.Int);
            param.Value = ddlShipper.SelectedValue;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@freight", SqlDbType.Money);
            param.Value = txtFreight.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipname", SqlDbType.NVarChar, 40);
            param.Value = txtShipName.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipaddress", SqlDbType.NVarChar, 60);
            param.Value = txtShipAddress.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipcity", SqlDbType.NVarChar, 15);
            param.Value = txtShipCity.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipregion", SqlDbType.NVarChar, 15);
            if (txtShipRegion.Text.Trim().Length == 0)
                param.Value = DBNull.Value;
            else
                param.Value = txtShipRegion.Text;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shippostalcode", SqlDbType.NVarChar, 10);
            if (txtShipPostalCode.Text.Trim().Length == 0)
                param.Value = DBNull.Value;
            else
                param.Value = int.Parse(txtShipPostalCode.Text);
            cmd.Parameters.Add(param);

            param = new SqlParameter("@shipcountry", SqlDbType.NVarChar, 15);
            param.Value = ddlCountry.Text;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
        }
        catch (System.FormatException)
        {
            Response.Write("<script>alert('" + "Invalid Date!" + "')</script>");
        }
        catch (System.Exception ex)
        {
            Response.Write("<script>alert('" + "Cannot add Data, check again!" + "')</script>");
        }
        finally
        {
            con.Close();
        }
    }

    private void DeleteOrder()
    {
        try
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "DeleteOrder";

            SqlParameter param = new SqlParameter("@orderid", SqlDbType.Int);
            param.Value = int.Parse(txtOrderID.Text);
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            Response.Write("<script>alert('" + "Please remove all Order Details before deleting!" + "')</script>");
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + "Cannot delete Data, check again!" + "')</script>");
        }
        finally
        {
            con.Close();
        }
    }

    private void Search()
    {
        try
        {
            con.Open();
            SqlParameter param;
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            switch (ddlSearch.Text.Trim())
            {
                case "Order ID":
                    cmd.CommandText = "SearchOrdersByID";
                    param = new SqlParameter("@orderid", SqlDbType.Int);
                    param.Value = int.Parse(txtSearch.Text);
                    cmd.Parameters.Add(param);
                    break;
                case "Customer":
                    cmd.CommandText = "SearchOrdersByCustomerID";
                    param = new SqlParameter("@custid", SqlDbType.Int);
                    if (ddlSearchValue.SelectedIndex == 0)
                        param.Value = DBNull.Value;
                    else param.Value = ddlSearchValue.SelectedValue;
                    cmd.Parameters.Add(param);
                    break;
                case "Employee":
                    cmd.CommandText = "SearchOrdersByEmployeeID";
                    param = new SqlParameter("@empid", SqlDbType.Int);
                    param.Value = ddlSearchValue.SelectedValue;
                    cmd.Parameters.Add(param);
                    break;
                case "Order Date":
                    cmd.CommandText = "SearchOrdersByOrderDate";
                    param = new SqlParameter("@orderdatefrom", SqlDbType.DateTime);
                    param.Value = DateTime.Parse(txtDateSearchFrom.Text);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@orderdateto", SqlDbType.DateTime);
                    param.Value = DateTime.Parse(txtDateSearchTo.Text);
                    cmd.Parameters.Add(param);
                    break;
                case "Required Date":
                    cmd.CommandText = "SearchOrdersByRequiredDate";
                    param = new SqlParameter("@requireddatefrom", SqlDbType.DateTime);
                    param.Value = DateTime.Parse(txtDateSearchFrom.Text);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@requireddateto", SqlDbType.DateTime);
                    param.Value = DateTime.Parse(txtDateSearchTo.Text);
                    cmd.Parameters.Add(param);
                    break;
                case "Shipped Date":
                    cmd.CommandText = "SearchOrdersByShippedDate";
                    param = new SqlParameter("@shippeddatefrom", SqlDbType.DateTime);
                    param.Value = DateTime.Parse(txtDateSearchFrom.Text);
                    cmd.Parameters.Add(param);
                    param = new SqlParameter("@shippeddateto", SqlDbType.DateTime);
                    param.Value = DateTime.Parse(txtDateSearchTo.Text);
                    cmd.Parameters.Add(param);
                    break;
                case "Not yet Shipped":
                    cmd.CommandText = "SearchOrdersByNotYetShipped";
                    break;
            }
            da = new SqlDataAdapter(cmd);
            ds = new DataSet();
            da.Fill(ds);

            gvOrders.DataSource = ds.Tables[0];
            gvOrders.PageIndex = 0;

        }
        catch (Exception e)
        {
            gvOrders.DataSource = null;
        }
        finally
        {
            ViewState["gv"] = gvOrders.DataSource;
            gvOrders.DataBind();
            con.Close();
            if (gvOrders.Rows.Count == 0)
                lblNotFound.Text = "Not found!";
            else
                lblNotFound.Text = "";
        }

    }
    #endregion

    #region LoadDDL
    private void LoadDDLCustomers()
    {
        //try
        //{
        SqlDataAdapter da = new SqlDataAdapter("SELECT custid, companyname FROM Sales.Customers", con);
        DataTable dt = new DataTable("Customers");
        da.Fill(dt);

        DataRow r = dt.NewRow();
        r[0] = DBNull.Value;
        r[1] = "";
        dt.Rows.InsertAt(r, 0);

        ddlCustomer.DataSource = dt;
        ddlCustomer.DataTextField = "companyname";
        ddlCustomer.DataValueField = "custid";
        ddlCustomer.DataBind();
        //}
        //catch (System.Exception ex)
        //{
        //    Response.Write("<script>alert('" + "Load Fail!" + "')</script>");
        //}
    }

    private void LoadDDLEmployees()
    {
        //try
        //{
        SqlDataAdapter da = new SqlDataAdapter("SELECT empid, firstname+ ' ' + lastname AS fullname FROM HR.Employees", con);
        DataTable dt = new DataTable("Customers");
        da.Fill(dt);

        ddlEmployee.DataSource = dt;
        ddlEmployee.DataTextField = "fullname";
        ddlEmployee.DataValueField = "empid";
        ddlEmployee.DataBind();
        //}
        //catch (System.Exception ex)
        //{
        //    Response.Write("<script>alert('" + "Load Fail!" + "')</script>");
        //}
    }

    private void LoadDDLShippers()
    {
        //try
        //{
        SqlDataAdapter da = new SqlDataAdapter("SELECT shipperid, companyname FROM Sales.Shippers", con);
        DataTable dt = new DataTable("Shippers");
        da.Fill(dt);

        ddlShipper.DataSource = dt;
        ddlShipper.DataTextField = "companyname";
        ddlShipper.DataValueField = "shipperid";
        ddlShipper.DataBind();
        //}
        //catch (System.Exception ex)
        //{
        //    Response.Write("<script>alert('" + "Load Fail!" + "')</script>");
        //}
    }

    private void LoadDDLSearchValue(int x)
    {
        try
        {
            if (x == 1)
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT custid, companyname FROM Sales.Customers", con);
                DataTable dt = new DataTable("Customers");
                da.Fill(dt);

                DataRow r = dt.NewRow();
                r[0] = DBNull.Value;
                r[1] = "";
                dt.Rows.InsertAt(r, 0);

                ddlSearchValue.DataSource = dt;
                ddlSearchValue.DataTextField = "companyname";
                ddlSearchValue.DataValueField = "custid";
                ddlSearchValue.DataBind();
            }
            else
            {

                SqlDataAdapter da = new SqlDataAdapter("SELECT empid, firstname+ ' ' + lastname AS fullname FROM HR.Employees", con);
                DataTable dt = new DataTable("Customers");
                da.Fill(dt);

                ddlSearchValue.DataSource = dt;
                ddlSearchValue.DataTextField = "fullname";
                ddlSearchValue.DataValueField = "empid";
                ddlSearchValue.DataBind();
            }

        }
        catch (System.Exception ex)
        {
            Response.Write("<script>alert('" + "Cannot connect to database!" + "')</script>");
        }
    }
    #endregion

    #region GridViewEvent
    protected void gvOrders_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gvOrders.PageIndex = e.NewPageIndex;
        gvOrders.DataSource = ViewState["gv"];
        gvOrders.DataBind();
    }
    protected void gvOrders_SelectedIndexChanged(object sender, EventArgs e)
    {
        try
        {
            GridViewRow r = gvOrders.SelectedRow;
            txtOrderID.Text = Server.HtmlDecode(r.Cells[1].Text);
            if (Server.HtmlDecode(r.Cells[2].Text).Trim().Length == 0)
                ddlCustomer.SelectedIndex = 0;
            else
                ddlCustomer.SelectedValue = Server.HtmlDecode(r.Cells[2].Text);
            ddlEmployee.SelectedValue = Server.HtmlDecode(r.Cells[3].Text);
            txtOrderDate.Text = Server.HtmlDecode(r.Cells[4].Text);
            txtRequiredDate.Text = Server.HtmlDecode(r.Cells[5].Text);
            txtShippedDate.Text = Server.HtmlDecode(r.Cells[6].Text);
            ddlShipper.SelectedValue = Server.HtmlDecode(r.Cells[7].Text);
            txtFreight.Text = Server.HtmlDecode(r.Cells[8].Text);
            txtShipName.Text = Server.HtmlDecode(r.Cells[9].Text);
            txtShipAddress.Text = Server.HtmlDecode(r.Cells[10].Text);
            txtShipCity.Text = Server.HtmlDecode(r.Cells[11].Text);
            txtShipRegion.Text = Server.HtmlDecode(r.Cells[12].Text);
            txtShipPostalCode.Text = Server.HtmlDecode(r.Cells[13].Text);
            ddlCountry.SelectedValue = Server.HtmlDecode(r.Cells[14].Text);

            btnUpdate.Enabled = true;
            btnDelete.Enabled = true;
            btnAdd.Enabled = false;
        }
        catch (System.Exception ex)
        {

        }
    }
    protected void gvOrders_Sorting(object sender, GridViewSortEventArgs e)
    {
        DataTable dataTable = ViewState["gv"] as DataTable;

        if (dataTable != null)
        {
            DataView dataView = new DataView(dataTable);
            if (ViewState["SortDirection"].ToString().Trim().Equals("ASC"))
            {
                dataView.Sort = e.SortExpression + " " + "DESC";
                ViewState["SortDirection"] = "DESC";
            }
            else
            {
                dataView.Sort = e.SortExpression + " " + "ASC";
                ViewState["SortDirection"] = "ASC";
            }
            gvOrders.DataSource = dataView;
            gvOrders.DataBind();
            ViewState["gv"] = dataView.ToTable();
        }
    }
    #endregion

    #region ControlEvent
    protected void btnNew_Click(object sender, EventArgs e)
    {
        ClearData();
    }

    protected void btnAdd_Click(object sender, EventArgs e)
    {
        AddOrder();
        ClearData();
    }

    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        DateTime dt;
        try
        {
            dt = DateTime.Parse(txtOrderDate.Text);
            if ((int)DateTime.Now.ToOADate() - (int)dt.ToOADate() > 1)
                Response.Write("<script>alert('" + "Cannot modify order after 24 hours!" + "')</script>");
            else
            {
                UpdateOrder();
            }
        }
        catch (FormatException ex) 
        {
            Response.Write("<script>alert('" + "Invalid Date!" + "')</script>");
        }
        catch (System.Exception ex)
        {
            Response.Write("<script>alert('" + "Cannot update data!" + "')</script>");
        }
        finally
        {
            ClearData();
        }
    }

    protected void btnDelete_Click(object sender, EventArgs e)
    {
        //String conform = "<script>if (confirm('Are you sure?')){DeleteOrder();}</script>";
        //Response.Write(conform );
        DeleteOrder();
        ClearData();
    }

    protected void btnSearch_Click(object sender, EventArgs e)
    {
        Search();
    }
    protected void btnReload_Click(object sender, EventArgs e)
    {
        ClearData();
        gvOrders.PageIndex = 0;
    }
    protected void btnDetail_Click(object sender, EventArgs e)
    {
        if (txtOrderID.Text.Trim().Length > 0)
            Response.Redirect("OrderDetails.aspx?ID=" + txtOrderID.Text);
    }
    protected void ddlSearch_TextChanged(object sender, EventArgs e)
    {
        ClearData();

        string[] s = { "Order Date", "Required Date", "Shipped Date", "Not yet Shipped" };
        if (s.Contains(ddlSearch.Text))
        {
            txtSearch.Visible = false;
            ddlSearchValue.Visible = false;
            txtDateSearchFrom.Visible = true;
            txtDateSearchTo.Visible = true;
            lblSearchFrom.Visible = true;
            lblSearchTo.Visible = true;
            if (ddlSearch.Text.Equals("Not yet Shipped"))
            {
                txtDateSearchFrom.Enabled = false;
                txtDateSearchTo.Enabled = false;
            }
            else
            {
                txtDateSearchFrom.Enabled = true;
                txtDateSearchTo.Enabled = true;
            }
        }
        else if (ddlSearch.Text.Equals("Order ID"))
        {
            txtSearch.Visible = true;
            ddlSearchValue.Visible = false;
            txtDateSearchFrom.Visible = false;
            txtDateSearchTo.Visible = false;
            lblSearchFrom.Visible = false;
            lblSearchTo.Visible = false;
        }
        else
        {
            ddlSearchValue.Visible = true;
            txtSearch.Visible = false;
            txtDateSearchFrom.Visible = false;
            txtDateSearchTo.Visible = false;
            lblSearchFrom.Visible = false;
            lblSearchTo.Visible = false;
            if (ddlSearch.Text.Equals("Customer"))
                LoadDDLSearchValue(1);
            else
                LoadDDLSearchValue(2);
        }
    }
    #endregion

}
